alter table neto.cap05 add arquivo sysname
go
alter table neto.cap06 add arquivo sysname
go
alter table neto.cap10 add arquivo sysname
go

alter table neto.cap05 add lancod int
go
alter table neto.cap06 add lancod int
go
alter table neto.cap10 add lancod int
go

alter table neto.cap05 add barcod sysname
go
alter table neto.cap06 add barcod sysname
go
alter table neto.cap10 add barcod sysname
go

alter table neto.cap05 add valor money
go
alter table neto.cap06 add valor money
go
alter table neto.cap10 add valor money
go

update neto.cap05 set arquivo = 'CAP05.07 e 10032008.1.RET', barcod = substring( col001, 38, 44)
update neto.cap06 set arquivo = 'CAP06 e 07032008.1.RET', barcod = substring( col001, 38, 44)
update neto.cap10 set arquivo = 'CAP10 e 11032008.1.RET', barcod = substring( col001, 38, 44) 
go

update neto.cap05 set valor = CONVERT(MONEY, SUBSTRING(col001, 82, 12)) / 100 where substring(col001, 1, 1) = 'G'
update neto.cap06 set valor = CONVERT(MONEY, SUBSTRING(col001, 82, 12)) / 100  where substring(col001, 1, 1) = 'G'
update neto.cap10 set valor = CONVERT(MONEY, SUBSTRING(col001, 82, 12)) / 100  where substring(col001, 1, 1) = 'G'
go

update neto.cap05 set lancod = CONVERT(int, SUBSTRING(barcod, 34, 9) ) where substring(col001, 1, 1) = 'G'
update neto.cap06 set lancod = CONVERT(int, SUBSTRING(barcod, 34, 9) ) where substring(col001, 1, 1) = 'G'
update neto.cap10 set lancod = CONVERT(int, SUBSTRING(barcod, 34, 9) ) where substring(col001, 1, 1) = 'G'
go

select * from neto.cap05 
select * from neto.cap06
select * from neto.cap10

		select max( coalesce(valor, 0) ) as valores from neto.cap05 
union 	select max( coalesce(valor, 0) ) from neto.cap06
union 	select max( coalesce(valor, 0) ) from neto.cap10


select sum( valores ) from 
	(
			select sum( coalesce(valor, 0) ) as valores from neto.cap05 
	union 	select sum( coalesce(valor, 0) ) from neto.cap06
	union 	select sum( coalesce(valor, 0) ) from neto.cap10
	) TABELA


select SUBSTRING(col001, 82, 12), CONVERT(int, SUBSTRING(barcod, 34, 9) ), * from neto.cap05 where barcod like '%004625709%'
select * from neto.cap06 where barcod like '%004625709%' 
select * from neto.cap10 where barcod like '%004625709%' 

select substring( '817500000002000024092009803050205001004625709052', 37, 9)
select substring( '81750000000000024092008030502050000462570905', 34, 9)

select * from neto.cap05 where lancod = 4625709
select * from neto.cap06 where lancod = 4625709
select * from neto.cap10 where lancod = 4625709

select barcod from 
	(
	select * from neto.cap05 union
	select * from neto.cap06 union
	select * from neto.cap10 
	) as arquivos
	where valor is not null
	and lancod = 3953730

select bxa_autenticacao, bxa_nu_processo, * from trb_baixa where bxa_autenticacao like 'Lote%001541%' 

select bxa_nu_processo, bxa_autenticacao  from trb_baixa where bxa_autenticacao like 'Lote%001541%' union 
select bxa_nu_processo, bxa_autenticacao  from trb_baixa where bxa_autenticacao like 'Lote%001540%' union 
select bxa_nu_processo, bxa_autenticacao  from trb_baixa where bxa_autenticacao like 'Lote%001539%'

select * from trb_lancamento
where lan_cod in 
		(
			select lancod from 
				(
				select * from neto.cap05 union
				select * from neto.cap06 union
				select * from neto.cap10 
				) as arquivos
				where valor is not null	
		)
		and lan_cod in ( select lan_cod from trb_baixa )




select * from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP05.07 e 10032008.RET'
select * from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP06 e 07032008.RET'
select * from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP10 e 11032008.ret'


select * from neto.cap05 where lancod = 4625709
select * from neto.cap06 where lancod = 4625709
select * from neto.cap10 where lancod = 4625709

select * from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP05.07 e 10032008.RET' and bxi_identificador like '%4625709%'

select * from neto.cap05 where col001 not in (select bxi_linha from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP05.07 e 10032008.RET')
select * from neto.cap06 where col001 not in (select bxi_linha from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP06 e 07032008.RET')
select * from neto.cap10 where col001 not in (select bxi_linha from trb_lauro_baixa_bancaria_item where BXI_ARQUIVO = 'CAP10032008.RET')

EXEC TRB_LAURO_BAIXAR_RETORNO NULL

SELECT SUBSTRING( '81750000000094824092008043004010000421585101', 20, 8)
